#coding=utf-8
from sqlalchemy import and_

import pytest
from domain.DeviceInfo import DeviceInfo
from domain.PackageInfo import PackageInfo
from domain.ServiceInfo import ServiceInfo
from domain.FunctionInfo import FunctionInfo
from domain.BasicInfo import BasicInfo
from sqlalchemy import asc
from domain.DataDict import DataDict
from domain.CaseInfo import CaseInfo
from domain.DataInfo import DataInfo

class QuerySql:

    '''
    获取包信息
    '''
    def queryPackage(self,end,dbConnect):
        packageInfo = dbConnect.query(PackageInfo.app_package, PackageInfo.app_activity, PackageInfo.app_simple).filter(
            PackageInfo.end == end).filter(PackageInfo.del_flg == 0).all()
        return packageInfo

    '''
    获取环境信息
    '''
    def queryEnv(self, env,dbConnect):
        envInfo = dbConnect.query(ServiceInfo.ip, ServiceInfo.port, ServiceInfo.username, ServiceInfo.passwd).filter(ServiceInfo.env == env).filter(
            ServiceInfo.del_flg == 0).all()
        return envInfo

    '''
    获取设备信息
    '''
    def queryDevice(self, deviceSel,dbConnect):
        deviceInfo = dbConnect.query(DeviceInfo.device_desc, DeviceInfo.platform_name, DeviceInfo.platform_version,
                              DeviceInfo.device_name, DeviceInfo.udid,DeviceInfo.ip).filter(
            DeviceInfo.device_name == deviceSel).filter(DeviceInfo.del_flg == 0).all()
        return deviceInfo

    '''
    获取用例信息
    '''
    def queryFunction(self, end,dbConnect,storyId,caseId,assignId):
        if storyId == None or storyId == '':
            functionInfo = dbConnect.query(FunctionInfo.step_no, FunctionInfo.step_name, FunctionInfo.element_type,
                                           FunctionInfo.element, FunctionInfo.action_no, DataInfo.data_info,
                                           CaseInfo.assert_info, BasicInfo.end,FunctionInfo.order,CaseInfo.case_name).join(CaseInfo,
                                                                    CaseInfo.id == FunctionInfo.case_id).join(BasicInfo,
                                                                                     BasicInfo.function_no == CaseInfo.function_no).join(DataInfo,and_(DataInfo.function_id == FunctionInfo.id ,DataInfo.assign_id == assignId), isouter=True) \
                .filter(BasicInfo.end == end).filter(FunctionInfo.del_flg == "0").filter(CaseInfo.id == caseId)\
                .order_by(asc(FunctionInfo.case_id),asc(FunctionInfo.order)).all()
        else:
            functionInfo = dbConnect.query(FunctionInfo.step_no, FunctionInfo.step_name, FunctionInfo.element_type,
                                           FunctionInfo.element, FunctionInfo.action_no, DataInfo.data_info,
                                           CaseInfo.assert_info, BasicInfo.end, FunctionInfo.order,
                                           CaseInfo.case_name).join(CaseInfo,
                                                                    CaseInfo.id == FunctionInfo.case_id).join(BasicInfo,
                                                                    BasicInfo.function_no == CaseInfo.function_no).join(DataInfo,and_(DataInfo.function_id == FunctionInfo.id ,DataInfo.assign_id == assignId), isouter=True) \
                .filter(BasicInfo.end == end).filter(FunctionInfo.del_flg == "0").filter(
                BasicInfo.id.in_(storyId)).filter(CaseInfo.id == caseId).order_by(asc(FunctionInfo.case_id),asc(FunctionInfo.order)).all()
        return functionInfo

    '''
    获取case基本信息
    '''
    def queryCase(self, storyId, dbConnect,end1):
        if storyId == None or storyId == '':
            envInfo = dbConnect.query(CaseInfo.id).join(BasicInfo,
                      CaseInfo.function_no == BasicInfo.function_no).filter(CaseInfo.del_flg == "0").filter(BasicInfo.end == end1).order_by(CaseInfo.id).all()
        else:
            if len(storyId.split(',')) > 1:
                envInfo = dbConnect.query(CaseInfo.id).join(BasicInfo,
                                                            CaseInfo.function_no == BasicInfo.function_no).filter(
                    CaseInfo.del_flg == "0").filter(BasicInfo.end == end1).filter(BasicInfo.id.in_(storyId)).order_by(
                    CaseInfo.id).all()
            else:
                envInfo = dbConnect.query(CaseInfo.id).join(BasicInfo,
                                                            CaseInfo.function_no == BasicInfo.function_no).filter(
                    CaseInfo.del_flg == "0").filter(BasicInfo.end == end1).filter(BasicInfo.id==storyId).order_by(
                    CaseInfo.id).all()
        # envInfoList = []
        # for env in envInfo:
        #     envInfoList.append(env)
        return envInfo

    '''
    获取字典表信息
    '''
    def queryDataDict(self, end, dbConnect):
        dataInfo = dbConnect.query(DataDict.key, DataDict.value).filter(DataDict.key == end).all()
        return dataInfo